package com.cg;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.HashMap;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.db.DBConnection;

public class OrgManage {
	public JSONObject initJSON(String date){
		//创建数据库连接
		Connection conn=new DBConnection().getDefConn();
		Statement stmt=null;
		SimpleDateFormat sf1=new SimpleDateFormat("yyyyMMddHHmmss");
		SimpleDateFormat sf2=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try {
			stmt=conn.createStatement();
			HashMap<String, String> dept=new HashMap<String, String>();
			HashMap<String, String> deptCode=new HashMap<String, String>();
			
			JSONObject root=new JSONObject();
			JSONObject enterprise=new JSONObject();
			JSONArray departments=new JSONArray();
			JSONArray users=new JSONArray();
			JSONObject child;

			//查询组织表
			String department="select id,name,is_group,type,path,code,org_account_id,ext_attr_3 tax,is_deleted,is_enable,short_name  " +
					" from org_unit where update_time >= '"+sf2.format(sf1.parse(date))+"' order by path ";
			ResultSet ret=stmt.executeQuery(department);
			//循环查到的组织信息
			while (ret.next()) {
				dept.put(ret.getString("path"), ret.getString("id"));
				dept.put(ret.getString("id"), ret.getString("code"));
				deptCode.put(ret.getString("path"), ret.getString("code"));
				child=new JSONObject();
				child.put("id", ret.getString("id"));
				child.put("name", ret.getString("name"));
				child.put("code", ret.getString("code")==null?"":ret.getString("code"));
				child.put("superior", dept.get(ret.getString("path").substring(0, ret.getString("path").length()-4))==null?"":dept.get(ret.getString("path").substring(0, ret.getString("path").length()-4)));
				child.put("type", ret.getString("type"));
				child.put("shortName", ret.getString("short_name"));
				child.put("isDelete", ret.getString("is_deleted"));
				child.put("enabled", ret.getString("is_enable"));
				child.put("tax", ret.getString("tax")==null?"":ret.getString("tax"));
				//放入部门数组中
				departments.add(child);
			}
			//查询人员表（ORG_MEMBER）关联人员账号表（ORG_PRINCIPAL） 关联职务职级表（org_level） 关联 岗位表（org_post） 关联组织表（org_unit）
			String member="select m.id,s.LOGIN_NAME,m.name username,l.name postname,isnull(m.EXT_ATTR_1 ,isnull(m.EXT_ATTR_3,' ')) phone," +
					"m.EXT_ATTR_2 email,case m.EXT_ATTR_11 when 1 then '男' when 2 then '女' else '不详' end  sex,u.id deptid,m.org_account_id accid,m.code,lel.name levelname,m.state,m.is_enable,m.is_deleted " +
					"from ORG_MEMBER m left outer join ORG_PRINCIPAL s on s.MEMBER_ID=m.id,org_level lel," +
					"org_post l,org_unit u where m.org_post_id=l.id " +
					" and m.IS_ADMIN=0 and m.org_level_id=lel.id and u.id=m.org_department_id " +
					" and m.update_time >= '"+sf2.format(sf1.parse(date))+"' ";
			ret=stmt.executeQuery(member);
			//循环查询到的数据
			while (ret.next()) {
				child=new JSONObject();
				child.put("id", ret.getString("id"));
				child.put("name", ret.getString("username"));
				child.put("login_name", ret.getString("LOGIN_NAME")==null?"":ret.getString("LOGIN_NAME"));
				child.put("code", ret.getString("code")==null?"":ret.getString("code"));
				child.put("orgPost", ret.getString("postname")==null?"":ret.getString("postname"));
				//child.put("phone", ret.getString("phone")==null?"":ret.getString("phone"));
				//child.put("email", ret.getString("email")==null?"":ret.getString("email"));
				//child.put("sex", ret.getString("sex")==null?"":ret.getString("sex"));
				child.put("orgDepartmentId",ret.getString("deptid"));
				child.put("orgAccountId",ret.getString("accid"));
				child.put("orgLevel", ret.getString("levelname")==null?"":ret.getString("levelname"));
				child.put("state", ret.getString("state"));
				child.put("enabled", ret.getString("is_enable"));
				child.put("isDelete", ret.getString("is_deleted"));
				users.add(child);
			}
			enterprise.put("departments", departments);
			enterprise.put("users", users);
			root.put("enterprise", enterprise);
			return root;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}finally {
			try {
				stmt.close();
				conn.close();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
	}
}
